package com.victualler.business.customer.repository.impl;

import com.victualler.business.customer.dto.dataview.MounthQuantityDto;
import com.victualler.business.customer.dto.print.PreCustomerPrintDto;
import com.victualler.business.customer.entity.PreCustomerOrderDao;
import com.victualler.business.customer.repository.PreCustomerOrderQueryRepository;
import com.victualler.core.base.repository.impl.BaseQueryRepositoryImpl;
import com.victualler.core.common.utils.ExceptionWapperUtil;
import com.victualler.core.common.utils.HQLConverterUtil;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author Guoshichang
 * @function 封装用于复杂sql查询的API. 为每个实体的repository层提供便利操作
 */
@Component
public class PreCustomerOrderQueryRepositoryImpl extends BaseQueryRepositoryImpl<PreCustomerOrderDao,Long> implements PreCustomerOrderQueryRepository {

    /**
     * @author Guoshichang
     * @since 12:51 on 2018/11/14.
     * @param orderNo orderNo订单号
     * @return List<InventoryPrintDto>
     * @Function  获取库存变更单的实体对象
     */
    public List<PreCustomerPrintDto> getExportModelList(String orderNo) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append("         main.order_no,main.customer_Name,main.send_Type,main.sum_Quantity, ");
        sql.append("         main.send_name,main.send_phone,main.send_address, ");
        sql.append("         main.reason,main.made_user, ");
        sql.append("         goods_main. NAME itemName, ");
        //将商品按照商品名称分组，将口味合并展示，并合计报货数量
        sql.append("         group_concat(CONCAT(goods_model_detail.NAME,ROUND(item.quantity),goods_model_detail.contour_name) separator ',')  itemModel, ");
        sql.append("         comm_dict.code_value itemType, ");
        sql.append("         ROUND(SUM(item.quantity)) quantity,item.note,goods_model_detail.bar_code barCode ");
        sql.append(" FROM ");
        sql.append(" pre_customer_item item  ");
        sql.append(" LEFT JOIN pre_customer_order main ON item.parent_id = main.id ");
        sql.append(" LEFT JOIN goods_main ON goods_main.id = item.goods_id ");
        sql.append(" LEFT JOIN goods_model_detail ON goods_model_detail.id = item.goods_model_id ");
        sql.append("  LEFT JOIN comm_dict ON comm_dict.code_code = goods_main.goods_type  ");
        sql.append(" AND goods_model_detail.parent_id = item.goods_id ");
        sql.append(" WHERE ");
        sql.append("         order_No = ? ");
        sql.append("  GROUP  BY  ");
        sql.append("         main.order_no,main.customer_name,main.send_Type,main.sum_Quantity,main.send_name, ");
        sql.append("         main.send_phone,main.send_address,main.reason,main.made_user,goods_main.group_type, ");
        sql.append("         goods_main.goods_type,item.quantity, ");
        sql.append("         goods_main.NAME,comm_dict.code_value,item.note,goods_model_detail.bar_code ");
        sql.append("  order by goods_main.group_type,goods_main.goods_type ,item.quantity desc,goods_main.name ");
        try {
            return HQLConverterUtil.findVOListBySql(sql.toString(),new Object[]{orderNo},PreCustomerPrintDto.class,this.getEntityManager());
        } catch (Exception e) {
            throw ExceptionWapperUtil.wapper(e);
        }
    }


    public List<MounthQuantityDto> getSumQuantityDataView() {
        StringBuffer sql = new StringBuffer();
        sql.append("select LEFT(create_date,10) date,sum(sum_quantity) value ");
        sql.append(" from ");
        sql.append(" pre_customer_order ");
        sql.append(" where ");
        sql.append(" DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(create_date) ");
        sql.append(" GROUP BY ");
        sql.append(" LEFT(create_date,10) ");
        try {
            return HQLConverterUtil.findVOListBySql(sql.toString(),new Object[]{},MounthQuantityDto.class,this.getEntityManager());
        } catch (Exception e) {
            throw ExceptionWapperUtil.wapper(e);
        }
    }


    /**
     * @author Guoshichang
     * @since 11:52 on 2019/1/31.
     * @param null
     * @return
     * @Function 获得前三十天内销量top10商品
     */
    public List<MounthQuantityDto> getQuantityByGoods() {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT a.date ,a.value from ( ");
        sql.append(" SELECT CONCAT(goods_main.name,'[',goods_model.name,']') date,sum(item.quantity) value ");
        sql.append(" from ");
        sql.append(" pre_customer_item item  ");
        sql.append(" LEFT JOIN goods_main goods_main ON item.goods_id = goods_main.id  ");
        sql.append(" LEFT JOIN goods_model_detail goods_model on item.goods_model_id = goods_model.id  ");
        sql.append("                                         and item.goods_id = goods_model.parent_id  ");
        sql.append(" where ");
        sql.append(" DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(item.create_date) ");
        sql.append(" GROUP BY ");
        sql.append(" item.goods_id,item.goods_model_id,LEFT (item.create_date, 10) ");
        sql.append(" ) a order by a.value desc limit   0,10 ");
        try {
            return HQLConverterUtil.findVOListBySql(sql.toString(),new Object[]{},MounthQuantityDto.class,this.getEntityManager());
        } catch (Exception e) {
            throw ExceptionWapperUtil.wapper(e);
        }
    }

    /**
     * @author Guoshichang
     * @since 11:52 on 2019/1/31.
     * @param null
     * @return
     * @Function 获得前三十天内销量top10的商家
     */
    public List<MounthQuantityDto> getQuantityByCustomer() {
        StringBuffer sql = new StringBuffer();
        sql.append(" select a.date,a.value from ( ");
        sql.append(" SELECT customer_name date,sum(sum_quantity) value ");
        sql.append(" from ");
        sql.append(" pre_customer_order  ");
        sql.append(" where ");
        sql.append(" DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(create_date) ");
        sql.append(" GROUP BY customer_id,customer_name ");
        sql.append(" ) a order by a.value desc limit   0,10 ");
        try {
            return HQLConverterUtil.findVOListBySql(sql.toString(),new Object[]{},MounthQuantityDto.class,this.getEntityManager());
        } catch (Exception e) {
            throw ExceptionWapperUtil.wapper(e);
        }
    }
}
